<?php
/*
2014年7月2日08时53分10秒
$this->phpmyadminToArray($path)
增加解析PHPMYADMIN导出SQL文件,将SQL文件解析到数组



sqlite 压缩命令: VACUUM

增加了缓存功能
//根据匹配度显示
//SELECT * FROM `ic_news` WHERE 1 order by length(title)-length(replace(title,'美','')) DESC

2013年6月6日 允许多个where 用and连起来的

#事务处理
$db->begin();
echo $db->pk(30)->set('porder='.rand(2,100))->update();
$db->commit();

#优化库所有表
p($db->opt());

function setEngine($table = null,$engine = 'MYISAM')

function setEngines($engine = 'MYISAM')
function tables()

max , sum, count,

*/

/*
**************************************
* Copyright (c) 2007 - 2008 边缘狂人 [中国蓝途]
* URL:http://www.kupebank.com
* E-mail:kupebank@163.com
* QQ:124181646
* filename:Mysql.class.php
* date:Tue Mar 16 02:38:06 CST 2010
* write by: 陈阳昇
* 工作室旗下中国蓝途,致力于开发优秀的网络产品
* description:
*
**************************************
*/


class db
{
	public $table;		//操作表
	public $prefix;		//操作表
	public $doTable;	//当前操作的全表名
	public $pk;			//主键
	public $lastSql;
	public $sqls;
	#protected 子类可使用参数, private 子类不可用
	protected $option = array();	//连惯操作时,记录参数
	protected $_db;		//数据库链接实例
	protected $config;		//

	public $server_id = 0;		// 用于链接多个数据库

	function __construct($table = '',$server_id = 0)
	{		
		$this->table = $table;
		if ($this->table)
		{
			$this->config = getconfig('db'.$server_id);			
			$this->prefix = $this->config['db_prefix'];
			$this->doTable = $this->prefix . $this->table;
			$this->server_id = $this->server_id;
			#设置一个默认主键
			if (!isset($this->pk))
			{
				$this->pk = 'id';
			}
		}
		$this->connect($server_id);
	}
	function setup()
	{
		$this->connect($this->server_id);
	}

	function connect($server_id)
	{
		
		/*
		if(is_object($this->_db[$server_id]))
		{
			load::loadClass('help')->show("数据库重复已经链接过了!!!");
			return true;
		}
		*/
		if(isset(load::$__dbConnect[$server_id]))
		{
			//load::loadClass('help')->show("数据库重复已经链接过了!!!");
			$this->_db[$server_id] = load::$__dbConnect[$server_id];
			return true;
		}
		$config = getconfig('db'.$server_id);


		if(!$config)
		{
			echo "数据库配置:{$server_id} , 不存在,请先填写配置";
		}

		if($config['db_type'] == 'mysql')
		{
			$dsn = "mysql:dbname={$config['db_name']};host={$config['db_host']};port={$config['db_port']}";
		}
		elseif($config['db_type'] == 'sqlite')
		{
			if(!file_exists($config['db_host']))
			{
				exit("sqlite文件不存在:{$config['db_host']}");
			}
			$dsn = "sqlite:{$config['db_host']}";
		}
		elseif($config['db_type'] == 'sqlite2')
		{
			$dsn = "sqlite2:{$config['db_host']}";
		}
		else
		{
			exit("请设置数据库DNS");			
		}
		try
		{
			$this->_db[$this->server_id] = new PDO($dsn, $config['db_user'], $config['db_password']);
		}
		catch (PDOException $e)
		{
			echo 'Connection failed: ' . $e->getMessage();
			exit;
		}
		$this->_db[$this->server_id]->exec("SET NAMES {$config['db_charset']}");
		$this->APP_RUN_SQL("SET NAMES {$config['db_charset']}");
		
		#记录成全局的,以免得重复链接
		load::$__dbConnect[$server_id] = $this->_db[$this->server_id];
		
		return true;
	}

	#查询SQL
	function query($sql)
	{
		/*$e = $this->_db[$this->server_id]->errorInfo();
			p($e);
			p("SQL执行错误:{$sql},".$e[2]);
			exit();*/
		$this->sqls[] = $this->lastSql = $sql;
		$this->APP_RUN_SQL($sql);
		return $this->_db[$this->server_id]->query($sql);
	}
	#执行SQL
	function exec($sql)
	{
		$this->sqls[] = $this->lastSql = $sql;
		$this->APP_RUN_SQL($sql);
		//return $this->_db[$this->server_id]->exec($sql);

		if(!$q = $this->_db[$this->server_id]->exec($sql))
		{
			$this->showError($this->_db[$this->server_id]->errorInfo());
		}
		else
		{
			return $q;
		}
	}
	#取得一个记录
	function fetchArray(&$query)
	{
		#0,1,2,3
		return $query->fetch(1);
	}
	#影响记录数
	/*
	function affectedRows()
	{
		return $this->_db[$this->server_id]->lastInsertId();
	}
	*/
	/*
	做记录
	*/
	function APP_RUN_SQL($sql)
	{
		load::$APP_RUN_SQL++;
		load::$APP_RUN_SQLS[] = $sql;
	}

	#取得最近一次ID
	function insertId()
	{
		$this->APP_RUN_SQL("insertId");
		return $this->_db[$this->server_id]->lastInsertId();
	}
	#关闭
	function close()
	{
		//mysql_close();
	}
	#事务支持
	function begin()
	{
		// 开始执行事务
		//mysql_query("begin");
		// 设置自动事务处理为0,1是提交即执行
		//mysql_query("set autocommit=0");
		return $this->_db[$this->server_id]->beginTransaction();
	}
	//别名
	function start()
	{
		return $this->begin();
	}

	function commit()
	{
		// 提交执行
		//mysql_query("commit");
		//mysql_query("set autocommit=1");
		return $this->_db[$this->server_id]->commit();
	}
	function end()
	{
		return $this->commit();
	}
	/*
	回滚操作
	*/
	function rollback()
	{
		//mysql_query("rollback");
		//mysql_query("set autocommit=1");

		$this->_db[$this->server_id]->rollBack();
	}
	#对SQL的值 转义
	function escape($value)
	{
		//return $value;  mysql_escape_string
		return addslashes($value);
	}

	#####################################

	function lastSql()
	{
		return $this->lastSql;
	}
	function allSql()
	{
		return $this->sqls;
	}

	/*
	支持连惯操作
	*/
	function __call($key, $value)
	{
		if (false !== strpos('|field|table|order|limit|group|values|set|as|having|', "|{$key}|"))
		{
			if (isset($value[0]) && $value[0])
			{
				$this->option[$key] = $value[0];
			}
			return $this;
		}
	}
	/*
	处理 left|right|inner
	*/
	final function LeftJoin($table, $on)
	{
		$this->option['join'][] = array('table' => ' Left Join ' . $this->prefix . $table, 'on' => $on);
		return $this;
	}
	final function RightJoin($table, $on)
	{
		$this->option['join'][] = array('table' => " Right Join " . $this->prefix . $table, 'on' => $on);
		return $this;
	}
	final function InnerJoin($table, $on)
	{
		$this->option['join'][] = array('table' => " INNER Join " . $this->prefix . $table, 'on' => $on);
		return $this;
	}
	final function SetCache($sec, $prefix = '')
	{
		$this->option['cache'] = $sec;
		$this->option['cachePrefix'] = $prefix;
		return $this;
	}
	#与setcache一样
	final function Cache($sec, $prefix = '')
	{
		$this->option['cache'] = $sec;
		$this->option['cachePrefix'] = $prefix;
		return $this;
	}
	function where($wstr)
	{
		$this->option['where'] = isset($this->option['where']) && $this->option['where'] ? "{$this->option['where']} AND {$wstr}" : $wstr;
		return $this;
	}
	function set($k,$v = null)
	{
		if($v == null)
		{
			$this->option['set'] = $k;
		}
		else
		{
			$this->option['set'] = array($k,$v);
		}
		return $this;
	}
	final function pk($id)
	{
		$this->option['where'] = isset($this->option['where']) && $this->option['where'] ? " AND {$this->pk}='{$id}'" : "  {$this->pk}='{$id}'";
		return $this;
	}
	/*
	增加LIKE 查询,可以多个
	*/
	final function Like($field, $value)
	{
		$this->option['like'][] = array($field, $value);
	}

	#取得一个记录
	final function find($option = '')
	{
		$this->limit(1);
		$rs = $this->findAll($option);
		return reset($rs);
	}


	#insert 的别名
	final function add($option = '', $action='INSERT')
	{
		return $this->insert($option, $action);
	}
	#insert 的别名
	final function create($option = '')
	{
		return $this->insert($option);
	}
	#修改表名
	final function setTable($table, $pk = false)
	{
		$this->table = $table;
		if (!$pk)
		{
			$this->pk = $pk;
		}
		return $this->doTable = $this->prefix . $this->table;
	}
	/*
	手动执行一个SQL
	$sql
	#__TABLE__ 		当前类全表名
	#__PRE__		表的前缀
	*/
	final function sql($sql)
	{
		$sql = str_replace(array('#__TABLE__', '#__PRE__', '{PREFIX}'), array($this->doTable, $this->prefix, $this->prefix), $sql);
		if (strtoupper(substr($sql, 0, 6)) == 'SELECT')
		{
			$query = &$this->query($sql);
			$rs = array();
			while ($rt = $this->fetchArray($query))
			{
				$rs[] = $rt;
			}
			return $rs;
		}
		else
		{
			return $this->query($sql);
		}
	}
/*	通过SQL查记录*/
	function findSql($sql)
	{
		$query = $this->query($sql);
		$rs = array();
		while($rt = $this->fetchArray($query))
		{
			$rs[] = $rt;
		}
		return $rs;
	}
	#delete from ic_t; 
	#update sqlite_sequence SET seq = 0 where name ='ic_t';

	//清空表
	function TRUNCATE($option = '')
	{
		if (!is_array($option))
		{
			$option = $this->option;
			$this->option = array();
		}
		$table = isset($option['table']) ? $this->prefix . $option['table'] : $this->doTable;

		if($this->config['db_type'] == 'sqlite')
		{
			$this->query("delete from $table ");
			$this->query("update sqlite_sequence SET seq = 0 where name ='{$table}' ");
			return true;
		}
		else
		{
			return $this->query("TRUNCATE TABLE $table ");
		}		
	}
	//别名
	function clear($option  = '')
	{
		$this->TRUNCATE($option);
	}
	function config()
	{
		return $this->config;
	}


	function _query($sql)
	{
		$sql = str_replace(array('#__TABLE__', '#__PRE__', '{PREFIX}'), array($this->doTable, $this->prefix, $this->prefix), $sql);
		return parent::query($sql);
	}

	/*
	统一功能SQL现实
	findAll
	delete
	insert
	update
	*/
	/*
	函数说明: 取得数据记录
	参数:
	$option = array('field' => '*',
	'where' => '条件',
	'limit' => '10',
	'table' => '表',
	'order' => '',
	'group' => '',
	)
	*/
	function findAll($option = '')
	{
		if (!is_array($option))
		{
			$option = $this->option;
			$this->option = array();
		}
		//P($option);
		//extract($option);
		$field = isset($option['field']) ? $option['field'] : '*';
		$table = isset($option['table']) ? $this->prefix . $option['table'] : $this->doTable;
		$sql = "SELECT {$field} FROM {$table} ";

		if (isset($option['as']))
		{
			$sql .= ' as ' . $option['as'];
		}
		if (isset($option['join']))
		{
			foreach ($option['join'] as $k => $v)
			{
				$sql .= " {$v['table']} ON {$v['on']}";
			}
		}
		//p($sql);
		if (isset($option['where']) && $option['where'])
		{
			$sql .= " WHERE {$option['where']}";
		}
		if (isset($option['group']))
		{
			$sql .= " GROUP BY {$option['group']}";
		}
		if (isset($option['having']))
		{
			$sql .= " HAVING {$option['having']}";
		}
		if (isset($option['order']))
		{
			$sql .= " ORDER BY {$option['order']}";
		}
		//limit 开始值, pagesize
		if (isset($option['limit']))
		{
			$sql .= " LIMIT {$option['limit']}";
		}

		$rs = array();

		//SQLITE返回的是对象
		$sql = str_replace(array('#__TABLE__', '{PREFIX}'), array($this->doTable, $this->prefix), $sql);
		$this->sqls[] = $this->lastSql = $sql;

//		load::$APP_RUN_SQL++;
		$this->APP_RUN_SQL($sql);

		
		if(!$prep = $this->_db[$this->server_id]->prepare($sql))
		{
			$this->showError($this->_db[$this->server_id]->errorInfo());
		}
		if(!$prep->execute())
		{
			$this->showError($prep->errorInfo());
		}
		$rs = $prep->fetchAll(2);

		return $rs;
	}
	function showError($errorInfo = array())
	{

		$msg = "<div class='error'><div class='header'>执行SQL错误,错误信息如下:</div>";
		$msg .= "<style>
		.error{
		border-bottom:1px solid #ccc;
		border-right:1px solid #ccc;
		line-height:30px;
		margin-left:300px;
		margin-right:300px;
		margin-top:50px;
	}

		.error .footer{
		text-align:right;
	}
		.error div{
		border-top:1px solid #ccc;
		border-left:1px solid #ccc;
		padding:10px;

	}
		.error .header{
		font-weight:bolder;
		background:#ddd;
	}
		</style>";
		echo $msg;
		foreach($errorInfo as $k => $v)
		{
			echo "<div>{$k}.{$v}</div>\n";
		}
		if(1)
		{
			echo "<div>SQL:".$this->lastSql."</div>\n";
		}

		echo "\n<div class='footer'><a href='http://www.kupebank.com'>来自:Kupe框架</a></div></div>";

		exit;

	}
	/*
	函数功能说明: 删除
	*/
	function delete($option = '')
	{
		if (!is_array($option))
		{
			$option = $this->option;
			$this->option = array();
		}
		$table = isset($option['table']) ? $this->prefix . $option['table'] : $this->doTable;
		$sql = "DELETE FROM {$table} ";
		if (isset($option['where']))
		{
			$sql .= " WHERE {$option['where']}";
		}
		if (isset($option['limit']))
		{
			$sql .= " LIMIT {$option['limit']}";
		}
		return $this->_db[$this->server_id]->exec($sql);
	}
	function remove($option = '')
	{
		return $this->delete($option);
	}
	/*
	函数说明: 插入记录
	$option.value => 可以是一维分组, 也可以是二维数组. 二维分组,表示插入多条记录.
	$option.table => 要插入到其它表
	返回: 最近插入的ID值
	*/


	function insert($option = '', $action='INSERT')
	{
		if (!is_array($option))
		{
			$option = $this->option;
			$this->option = array();
		}
		
		$keys = array_keys($option['values']);
		//一维数组
		$table = isset($option['table']) ? $this->prefix . $option['table'] : $this->doTable;
		$sql = "{$action} INTO {$table} (`" . implode('`,`', $keys) . "`) VALUES (:" . implode(',:', $keys) . ")";

		$prep = $this->_db[$this->server_id]->prepare($sql);
		$value_arr = array();
		foreach($option['values'] as $k => $v)
		{
			//$values_arr[":{$k}"] = $this->escape($v);
			$values_arr[":{$k}"] = $v;
		}
		if(!$prep->execute($values_arr))
		{
			$this->showError($prep->errorInfo());
		}
		$this->sqls[] = $this->lastSql = $sql;
		return $this->insertId();
	}


	/*
	功能函数:更新数据库
	$option 为数组
	$option.value	一组数组
	$option.table
	$option.pk
	*/
	function update($option = '')
	{
		if (!is_array($option))
		{
			$option = $this->option;
			$this->option = array();
		}
		$set = '';
		$set_arr = array();
		if (isset($option['values']))
		{
			foreach ($option['values'] as $k => $v)
			{
				if ($k != $this->pk)
				{
					$set .= ",`{$k}`=:{$k} ";
					$set_arr[":".$k] = $v;
				}
			}
		}

		$table = isset($option['table']) ? $this->prefix . $option['table'] : $this->doTable;
		$sql = "UPDATE {$table} SET " . substr($set, 1);
		if (isset($option['set']))
		{
			if(is_array($option['set']))
			{
				$sql .= "`{$option['set'][0]}`=:{$option['set'][0]}";
				$set_arr[":".$option['set'][0]] = $option['set'][1];
			}
			else
			{
				$sql .= $option['set'];
			}
		}





		if (isset($option['where']))
		{
			$sql .= " WHERE {$option['where']}";
			if (isset($option['values'][$this->pk]))
			{
				$sql .= " AND `{$this->pk}`='{$option['values'][$this->pk]}'";
			}
		}
		else
		{
			if (isset($option['values'][$this->pk]))
			{
				$sql .= " WHERE `{$this->pk}`='{$option['values'][$this->pk]}'";
			}
		}
		if (isset($option['limit']))
		{
			$sql .= " LIMIT {$option['limit']}";
		}
		if(!$prep = $this->_db[$this->server_id]->prepare($sql))
		{
			$this->showError($this->_db[$this->server_id]->errorInfo());
		}
		if(!$q = $prep->execute($set_arr))
		{
			$this->showError($prep->errorInfo());
		}
		$this->sqls[] = $this->lastSql = $sql;
		$this->APP_RUN_SQL($sql);
		return $q;
	}
	#
	#简化操作
	#
	function total($key = '*')
	{
		$rs = $this->field("COUNT({$key}) AS total")->find();
		return $rs['total'];
	}
	function count($key = '*')
	{
		$rs = $this->field("COUNT({$key}) AS total")->find();
		return $rs['total'];
	}
	function sum($key)
	{
		$rs = $this->field("SUM({$key}) AS total")->find();
		return $rs['total'];
	}
	function max($key = '*')
	{
		$rs = $this->field("max({$key}) AS total")->find();
		return $rs['total'];
	}
	function min($key = '*')
	{
		$rs = $this->field("min({$key}) AS total")->find();
		return $rs['total'];
	}
	/*
	显示本库所有列表
	*/
	function tables()
	{
		/*
		[queryString] => SHOW TABLE STATUS
    [Name] =>
    [Engine] =>
    [Version] =>
    [Row_format] =>
    [Rows] =>
    [Avg_row_length] =>
    [Data_length] =>
    [Max_data_length] =>
    [Index_length] =>
    [Data_free] =>
    [Auto_increment] =>
    [Create_time] =>
    [Update_time] =>
    [Check_time] =>
    [Collation] =>
    [Checksum] =>
    [Create_options] =>
    [Comment] =>
	*/
		$sql = "SHOW TABLE STATUS";
		$query = $this->query($sql);
		$rs = array();
		while($rt = $this->fetchArray($query))
		{
			$rs[] = array(
			'Name' => $rt->Name,
			'Engine' => $rt->Engine,
			'Version' => $rt->Version,
			'Row_format' => $rt->Row_format,
			'Rows' => $rt->Rows,
			'Avg_row_length' => $rt->Avg_row_length,
			'Data_length' => $rt->Data_length,
			'Max_data_length' => $rt->Max_data_length,
			'Index_length' => $rt->Index_length,
			'Data_free' => $rt->Data_free,
			'Auto_increment' => $rt->Auto_increment,
			'Create_time' => $rt->Create_time,
			'Update_time' => $rt->Update_time,
			'Check_time' => $rt->Check_time,
			'Collation' => $rt->Collation,
			'Checksum' => $rt->Checksum,
			'Create_options' => $rt->Create_options,
			'Comment' => $rt->Comment,
			);
		}
		return $rs;
	}
	#########################常用工具
	/*
	设置表引擎
	INNODB
	MYISAM
	*/
	function setEngine($table = null,$engine = 'MYISAM')
	{
		$table = $table ? $this->prefix . $table : $this->doTable;
		$sql = "ALTER TABLE  `{$table}` ENGINE = {$engine};";
		return $this->exec($sql);
	}
	function setEngines($engine = 'MYISAM')
	{
		$engine || $engine = 'MYISAM';
		$table = $this->tables();
		foreach ($table as $v)
		{
			$sql_arr[] = $sql = "ALTER TABLE  `{$v['Name']}` ENGINE = {$engine};";
			$this->exec($sql);
		}
		return true;
	}


	/*
	优化数据库
	$table 可以是数组, 单个表名, 或为 空  , 为空时,会优化整个数据库
	*/
	function OPTIMIZE($table = '')
	{

		if(is_array($table))
		{
			
		}
		elseif($table)
		{
			$table = array($table);
		}
		else
		{
			$table = $this->tables();
		}
		$sql = "OPTIMIZE TABLE ";
		foreach ($table as $v)
		{
			if(is_array($v))
			{
				$sql_arr[] = "`{$v['Name']}`";
			}
			else
			{
				$sql_arr[] = "`{$v}`";
			}
		}
		$sql .= implode(',', $sql_arr);
		return $this->query($sql);
	}
	/**
	自动将当前数据库修复
	参数 $table可以是 单个表名,也可以是 多个表的数组,如果为空,则自动获取当前库的所有表
	*/
	function REPAIR($table = '')
	{
		if(is_array($table))
		{
			
		}
		elseif($table)
		{
			$table = array($table);
		}
		else
		{
			$table = $this->tables();
		}
		$table = $this->tables();
		$sql = "REPAIR TABLE ";
		foreach ($table as $v)
		{
			$sql_arr[] = "`{$v['Name']}`";
		}
		$sql .= implode(',', $sql_arr);
		return $this->query($sql);
	}
	/**
	别名 REPAIR
	*/
	function opt($table = '')
	{
		return $this->OPTIMIZE($table);
	}
	/**
	创建一个数据库
	*/
	function createDB($name)
	{
		$sql = "CREATE DATABASE  `{$name}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		return $this->exec($sql);
	}
	function phpmyadminToArray($path)
	{
		$data = file_get_contents($path);
		$data = preg_replace('/--.+\n/isU','',$data);
		$data = preg_replace('/\/\*.+\*\/;/isU','',$data);

		$rs = explode(";\n",trim($data));
		$sqls = array();
		foreach($rs as $k => $v)
		{
			$v = trim($v);
			if(!$v) continue;
			$sqls[] = trim($v).'';
		}
		//echo $data;	p($sqls);
		return $sqls;
	}

	/*
	核心操作方法
	$this->input->xxxx();
	$this->output->xxxx();
	$this->db->xxxx();
	*/
    public function __get($name) 
    {
		return load::loadClass($name,null,null);
    }

	

	#释放资源
	/*function __destruct()
	{
	$this->close();
	}*/
}